﻿Imports System.Windows.Forms
Imports System.Diagnostics
Public Class frmNewType
    Dim _mysql As New ClsSQLhelper
    Dim strsql As String
    Dim Flg_OnLoad As Boolean = True
    Dim StackTrace As New StackTrace
    Dim StackFrames() As StackFrame

#Region "Function"

    Sub Load_AutoComplete_TypeName(Optional ByVal TypeCode As String = "%", Optional ByVal KindCode As String = "%", Optional ByVal Attribute As String = "%")
        Try
            strsql = "select Type_Name from typeitem where left(type_id,4) like '" & TypeCode & "' and mid(type_id,6,3) like '" & KindCode & "' and  right(trim(type_id),4) like '" & Attribute & "'  group by Type_Name"
            Dim DT_ItemName As New DataTable
            DT_ItemName = _mysql.GetMYSQLDataTable(strsql, "typeitem")
            If DT_ItemName.Rows.Count = 0 Then Exit Sub
            Dim ColItemName As New AutoCompleteStringCollection
            For i = 0 To DT_ItemName.Rows.Count - 1
                ColItemName.Add(DT_ItemName.Rows(i).Item("Type_Name"))
            Next
            Me.TxtTypeName.AutoCompleteCustomSource = ColItemName
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_AutoComplete_TypeName"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = strsql
                .ShowDialog()
            End With
        End Try
    End Sub
    Sub Load_AutoComplete_Type()
        Try
            strsql = "select Left(type_id,4) as typeid from typeitem group by Left(type_id,4)"
            Dim DT_ItemName As New DataTable
            DT_ItemName = _mysql.GetMYSQLDataTable(strsql, "typeitem")
            If DT_ItemName.Rows.Count = 0 Then Exit Sub
            Dim ColItemName As New AutoCompleteStringCollection
            For i = 0 To DT_ItemName.Rows.Count - 1
                ColItemName.Add(DT_ItemName.Rows(i).Item("typeid"))
            Next
            Me.Txtkind.AutoCompleteCustomSource = ColItemName


        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_AutoComplete_Type"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = strsql
                .ShowDialog()
            End With
        End Try
    End Sub
    Sub Load_AutoComplete_Kind()
        Try
            strsql = "select mid(type_id,6,3) as kind from typeitem where left(type_id,4) = '" & Me.TxtType.Text & "'  group by mid(type_id,6,3) "
            Dim DT_ItemName As New DataTable

            DT_ItemName = _mysql.GetMYSQLDataTable(strsql, "typeitem")
            If DT_ItemName.Rows.Count = 0 Then Exit Sub
            Dim ColItemName As New AutoCompleteStringCollection
            For i = 0 To DT_ItemName.Rows.Count - 1
                ColItemName.Add(DT_ItemName.Rows(i).Item("kind"))
            Next
            Me.TxtAttribute.AutoCompleteCustomSource = ColItemName
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_AutoComplete_Kind"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = strsql
                .ShowDialog()
            End With
        End Try
    End Sub
    Sub Load_AutoComplete_attribute()
        Try
            strsql = "select  right(trim(type_id),4) as attribute from typeitem where left(type_id,4) = '" & Me.TxtType.Text & "' and mid(type_id,6,3) ='" & Me.Txtkind.Text & "' group by  right(trim(type_id),4) "
            Dim DT_ItemName As New DataTable
            DT_ItemName = _mysql.GetMYSQLDataTable(strsql, "typeitem")
            If DT_ItemName.Rows.Count = 0 Then Exit Sub

            Dim ColItemName As New AutoCompleteStringCollection

            For i = 0 To DT_ItemName.Rows.Count - 1
                ColItemName.Add(DT_ItemName.Rows(i).Item("attribute"))
            Next
            Me.TxtAttribute.AutoCompleteCustomSource = ColItemName
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_AutoComplete_attribute"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = strsql
                .ShowDialog()
            End With
        End Try
    End Sub
    Sub Load_Category()
        Try
            strsql = "Select Cate_ID,Cate_Name from categories order by Cate_ID "
            Dim DT_Category As New DataTable
            DT_Category = _mysql.GetMYSQLDataTable(strsql, "categories")
            With CboCategory
                .DataSource = DT_Category
                .DisplayMember = "Cate_Name"
                .ValueMember = "Cate_ID"
                .SelectedIndex = 0
            End With
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_Category"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = strsql
                .ShowDialog()
            End With
        End Try
    End Sub
    Sub Load_Section()
        Try
            Me.CboSection.Text = ""
            Me.TxtType.Text = ""
            strsql = "Select Section_ID,Section_Name from sections where Cate_ID = " & Me.CboCategory.SelectedValue & " order by Section_ID "
            Dim DT_Section As New DataTable
            DT_Section = _mysql.GetMYSQLDataTable(strsql, "section")
            ' If DT_Section.Rows.Count = 0 Then Exit Sub
            With CboSection
                .DataSource = DT_Section
                .DisplayMember = "Section_Name"
                .ValueMember = "Section_ID"
                If DT_Section.Rows.Count > 0 Then
                    .SelectedIndex = 0
                    ' Me.TxtType.Text = Me.CboSection.SelectedValue
                Else
                    Me.CboSection.Text = ""
                    Me.CboAttribute.Text = ""



                End If
            End With

        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_Section"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = strsql
                .ShowDialog()
            End With
        End Try
    End Sub
    Sub Load_Attribute()
        Try

            strsql = "Select Attribute_ID,Attribute_Name from attribute where Cate_ID = " & Me.CboCategory.SelectedValue & " and Section_ID = " & Me.CboSection.SelectedValue & " order by Attribute_Name "
            Dim DT_Section As New DataTable
            DT_Section = _mysql.GetMYSQLDataTable(strsql, "section")
            ' If DT_Section.Rows.Count = 0 Then Exit Sub
            With CboAttribute
                .DataSource = DT_Section
                .DisplayMember = "Attribute_Name"
                .ValueMember = "Attribute_ID"
                If DT_Section.Rows.Count > 0 Then
                    .SelectedIndex = 0

                    Dim StrTypeID As String
                    If Me.CboCategory.Items.Count <> 0 Then
                        strsql = "Select ifnull(attribute_code,'') as Attribute_Code  from attribute where attribute_id  = " & CboAttribute.SelectedValue
                        StrTypeID = _mysql.MySQLExecuteScalar(strsql)
                        Me.TxtAttribute.Text = StrTypeID.ToString

                    End If

                Else
                    CboAttribute.Text = ""

                End If
            End With

        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "Load_Attribute"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = strsql
                .ShowDialog()
            End With
        End Try
    End Sub
    Function Validate_data() As Boolean
        Try
            Validate_data = False
            'Check Complete data
            If Me.TxtType.Text = "" Or Me.Txtkind.Text = "" Or Me.TxtAttribute.Text = "" Or Me.TxtTypeName.Text = "" Then
                MsgBox("กรุณากรอกข้อมูลให้ครบทุกช่อง", MsgBoxStyle.Critical, "กรุณาตรวจสอบข้อมูล")
                Exit Function
            End If
            'Check Numeric Data
            If IsNumeric(Me.TxtType.Text) = False Or IsNumeric(Me.Txtkind.Text) = False Or IsNumeric(Me.TxtAttribute.Text) = False Then
                MsgBox("กรุณาใส่เฉพาะเลข 0-9 เท่านั้น", MsgBoxStyle.Critical, "กรุณาตรวจสอบข้อมูล")
                Exit Function
            End If

            'Check Duplicate ID
            strsql = "select Count(Type_ID) as cc from typeitem where Type_ID = '" & Me.TxtType.Text & "-" & Me.Txtkind.Text & "-" & Me.TxtAttribute.Text & "'"
            If _mysql.MySQLExecuteScalar(strsql) > 0 Then
                MsgBox("รหัสนี้มีอยู่แล้วไม่สามารถทำรายการซ้ำได้", MsgBoxStyle.Critical, "พบข้อมูลซ้ำซ้อน")
                Exit Function
            End If


            Validate_data = True

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function
#End Region

    Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click

        Try
            If Validate_data() = True Then
                strsql = "Insert into typeitem (Type_ID,Type_Name,Section,Type_Desc) values ('" & Me.TxtType.Text & "-" & Me.Txtkind.Text & "-" & Me.TxtAttribute.Text & "','" & Me.TxtTypeName.Text & "','" & Me.CboSection.SelectedValue & "','" & Me.MemoDesc.Text & "')"
                If _mysql.MySQLExecute(strsql) > 0 Then
                    MsgBox("เพิ่มข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information, "ผลการเพิ่มข้อมูล")
                    Me.DialogResult = System.Windows.Forms.DialogResult.OK

                Else
                    With frmDebug
                        .lblFormName.Text = Me.Name
                        .lblFunctionName.Text = "OK_Button_Click"
                        .MemoErr_Description.Text = "SQL Error กรุณาตรวจสอบคำสั่ง"
                        .MemoSQL.Text = strsql
                        .ShowDialog()
                    End With

                End If
            Else
                Exit Sub
            End If


            'Add Log
            strsql = "Insert into transections_logs (trans_code,trans_datetime,trans_computer,trans_item) values (8,'" & Pn_Framework.MysqlDateTimeFormat(Now, True) & "','" & Pn_Framework.Get_Computername & "','" & Me.TxtType.Text & "-" & Me.Txtkind.Text & "-" & Me.TxtAttribute.Text & "') "
            Select Case (_mysql.MySQLExecute(strsql))
                Case -1
                    With frmDebug
                        .lblFormName.Text = Me.Name
                        .lblFunctionName.Text = "OK_Button_Click"
                        .MemoErr_Description.Text = "SQL Error กรุณาตรวจสอบคำสั่ง"
                        .MemoSQL.Text = strsql
                        .ShowDialog()
                    End With
            End Select
            Me.Dispose()
        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "OK_Button_Click"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = strsql
                .ShowDialog()
            End With
        End Try

    End Sub

    Private Sub Cancel_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel_Button.Click
        Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
        Me.Close()
    End Sub

    Private Sub GroupControl1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles GroupControl1.Paint

    End Sub

    Private Sub DlgAddType_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        StackFrames = StackTrace.GetFrames
        Load_Category()
        CboCategory.SelectedValue = frmType.CboCategories.SelectedValue
        Load_Section()
        CboSection.SelectedValue = frmType.CboSections.SelectedValue
        Load_Attribute()

        Load_AutoComplete_attribute()

        Load_AutoComplete_Type()

        Load_AutoComplete_TypeName()

        Me.CboCategory.SelectedValue = frmType.CboCategories.SelectedValue
        Me.CboSection.SelectedValue = frmType.CboSections.SelectedValue
        Try

            Dim StrTypeID As String
            If Me.CboCategory.Items.Count <> 0 Then
                strsql = "Select ifnull(Cate_code,'') as Cate_Code  from categories where Cate_id  = " & CboCategory.SelectedValue
                StrTypeID = _mysql.MySQLExecuteScalar(strsql)
                Me.TxtType.Text = StrTypeID.ToString

            End If


        Catch ex As Exception
            With frmDebug
                .lblFormName.Text = Me.Name
                .lblFunctionName.Text = "DlgAddType_Load"
                .MemoErr_Description.Text = ex.Message
                .MemoSQL.Text = strsql
                .ShowDialog()
            End With
        End Try

        Try

            ' Dim StrTypeID As String

            If Me.CboSection.Items.Count <> 0 Then
                strsql = "Select ifnull(Section_Code,'') as Section_Code from sections where Section_ID = " & CboSection.SelectedValue
                Me.Txtkind.Text = _mysql.MySQLExecuteScalar(strsql)
            End If

        Catch ex As Exception
            frmDebug.lblFormName.Text = Me.Name
            frmDebug.lblFunctionName.Text = "Load_Section"
            frmDebug.MemoErr_Description.Text = ex.Message
            frmDebug.MemoSQL.Text = strsql
            frmDebug.ShowDialog()
        End Try
        Flg_OnLoad = False
    End Sub

    Private Sub Txtkind_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles Txtkind.MouseClick
        Me.Txtkind.SelectionStart = 0
        Me.Txtkind.SelectionLength = Txtkind.Text.Length
    End Sub
    Private Sub Txtkind_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Txtkind.TextChanged
        Load_AutoComplete_attribute()
    End Sub
    Private Sub CboSection_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboSection.SelectedIndexChanged
        If Flg_OnLoad = True Then Exit Sub

        Load_Attribute()
        ' Load_AutoComplete_Kind()
        ' Load_AutoComplete_attribute()

        Try
            If Me.CboSection.Items.Count <> 0 Then
                strsql = "Select ifnull(Section_Code,'') as Section_Code from sections where Section_ID = " & CboSection.SelectedValue
                Me.Txtkind.Text = _mysql.MySQLExecuteScalar(strsql)
            End If


        Catch ex As Exception
            frmDebug.lblFormName.Text = Me.Name
            frmDebug.lblFunctionName.Text = "CboSection_SelectedIndexChanged"
            frmDebug.MemoErr_Description.Text = ex.Message
            frmDebug.MemoSQL.Text = strsql
            frmDebug.ShowDialog()
        End Try


    End Sub

    Private Sub TxtType_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles TxtType.MouseClick
        Me.TxtType.SelectionStart = 0
        Me.TxtType.SelectionLength = TxtType.Text.Length
    End Sub

    Private Sub TxtType_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtType.TextChanged
        Load_AutoComplete_Kind()
    End Sub

    Private Sub CboCategory_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboCategory.SelectedIndexChanged
        If Flg_OnLoad = True Then Exit Sub
        Load_Section()
        '  CboSection.SelectedValue = frmType.CboSections.SelectedValue


        'Load_AutoComplete_Type()

        Load_AutoComplete_TypeName()

        Try
            Dim StrTypeID As String
            If Me.CboCategory.Items.Count <> 0 Then
                strsql = "Select ifnull(Cate_Code,'') as Cate_Code  from categories where Cate_ID  = " & CboCategory.SelectedValue
                StrTypeID = _mysql.MySQLExecuteScalar(strsql)
                Me.TxtType.Text = StrTypeID.ToString

            End If


        Catch ex As Exception
            'Dim StrCallStack As String = ""
            'For Each stackframe As StackFrame In StackFrames
            '    StrCallStack = StrCallStack & stackframe.GetMethod().Name & vbCrLf

            'Next




            frmDebug.lblFormName.Text = Me.Name
            frmDebug.lblFunctionName.Text = "CboCategory_SelectedIndexChanged"
            frmDebug.MemoErr_Description.Text = ex.Message
            frmDebug.MemoSQL.Text = strsql

            frmDebug.ShowDialog()
        End Try


    End Sub

    Private Sub TxtAttribute_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles TxtAttribute.MouseClick
        Me.TxtAttribute.SelectionStart = 0
        Me.TxtAttribute.SelectionLength = TxtAttribute.Text.Length
    End Sub

    Private Sub TxtAttribute_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtAttribute.TextChanged

    End Sub

    Private Sub TxtTypeName_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles TxtTypeName.MouseClick
        Me.TxtTypeName.SelectionStart = 0
        Me.TxtTypeName.SelectionLength = TxtTypeName.Text.Length
    End Sub

    Private Sub TxtTypeName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtTypeName.TextChanged

    End Sub

    Private Sub CboAttribute_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboAttribute.SelectedIndexChanged
        Try
            If Flg_OnLoad = True Then Exit Sub
            If Me.CboAttribute.Items.Count <> 0 Then
                strsql = "Select ifnull(Attribute_Code,'') as Attribute_Code from attribute where Attribute_ID = " & CboAttribute.SelectedValue
                Me.TxtAttribute.Text = _mysql.MySQLExecuteScalar(strsql)
                Me.TxtTypeName.Text = Me.CboAttribute.Text
            End If

            'Load_AutoComplete_attribute()
        Catch ex As Exception
            frmDebug.lblFormName.Text = Me.Name
            frmDebug.lblFunctionName.Text = "CboAttribute_SelectedIndexChanged"
            frmDebug.MemoErr_Description.Text = ex.Message
            frmDebug.MemoSQL.Text = strsql
            frmDebug.ShowDialog()
        End Try


    End Sub
End Class
